`:top
`!Object-PL/SQL`! (`!`F33f`_`[Object`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Object-oriented_programming]`_`f-Procedural Language/`F33f`_`[Structured Query Language`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Structured_Query_Language]`_`f`! or simply `!O-PL/SQL`!) is a methodology of using the `F33f`_`[Oracle Corporation`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Oracle_Corporation]`_`f's `F33f`_`[procedural`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Procedural_programming]`_`f extension `F33f`_`[language`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Programming_language]`_`f for `F33f`_`[SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL]`_`f and the `F33f`_`[Oracle relational database`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Oracle_Database]`_`f.`:cite-ref-citdk-1-0[`F5bf`_`[1`#cite-note-citdk-1]`_`f] `:cite-ref-2[`F5bf`_`[2`#cite-note-2]`_`f] The additional features from version 7 and other improvements, lead to one of the large-scale environment implementations of the `F33f`_`[object-oriented database`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Object-oriented_database]`_`f paradigm.`:cite-ref-dbaora-3-0[`F5bf`_`[3`#cite-note-dbaora-3]`_`f]
Although PL/SQL's general `F33f`_`[syntax`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Syntax_(programming_languages)]`_`f formerly used to resemble that of `F33f`_`[Ada`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Ada_(programming_language)]`_`f or `F33f`_`[Pascal`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Pascal_(programming_language)]`_`f, there were many improvements that mainly include the `*Java embedding code`*`:cite-ref-stkover-4-0[`F5bf`_`[4`#cite-note-stkover-4]`_`f] and the `*object-oriented syntax`*`:cite-ref-etut-5-0[`F5bf`_`[5`#cite-note-etut-5]`_`f] inside the SQL.
The mixing and embedding of `F33f`_`[triggers`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Database_trigger]`_`f and `F33f`_`[stored procedures`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Stored_procedure]`_`f was one of the breakthrough points up to support the use of `F33f`_`[PL/SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=PL/SQL]`_`f in a `*OO`* paradigm.`:cite-ref-sams-6-0[`F5bf`_`[6`#cite-note-sams-6]`_`f] The inclusion in the SQL syntax of statements such as [class].[object], and the implementation of the object `*type`*`:cite-ref-top10-7-0[`F5bf`_`[7`#cite-note-top10-7]`_`f] (like any OO language), completed the minimum requisites to a mapping approach in an extended SQL language without use of specific mapping software.`:cite-ref-bales-8-0[`F5bf`_`[8`#cite-note-bales-8]`_`f]
>>Contents
• `F0af`_`[Autonomy, notoriety and importance of O-PL/SQL`#autonomy-notoriety-and-importance-of-o-pl-sql]`_`f
• `F0af`_`[A confusing of objects`#a-confusing-of-objects]`_`f
• `F0af`_`[The PL/SQL`#the-pl-sql]`_`f
• `F0af`_`[Examples of uses of syntax of O-PL/SQL`#examples-of-uses-of-syntax-of-o-pl-sql]`_`f
• `F0af`_`[See also`#see-also]`_`f
• `F0af`_`[Bibliography`#bibliography]`_`f
• `F0af`_`[References`#references]`_`f
• `F0af`_`[External sources`#external-sources]`_`f
-─
>>Autonomy, notoriety and importance of O-PL/SQL
The O-PSL/SQL isn't simply the use a version of a programming language but it's identified as `*how to use it`*, and it defines the autonomy of the theme.`:cite-ref-java2-9-0[`F5bf`_`[9`#cite-note-java2-9]`_`f] Each version of PL/SQL, starting from 7, brings so many innovations that it's impossible to treat such usages as sub-themes of PL/SQL. So big is that revolution that it establishes a real borderline between the language, that can be used as formerly, and the OO approach inside itself. It's just this approach that makes the theme important and the large-scale using has brought its notoriety.`:cite-ref-steve-10-0[`F5bf`_`[10`#cite-note-steve-10]`_`f]
>>A confusing of objects
There can be confusion of the notions of `*object of DBMS`* and of `*class object`*. This is very important as we live with both significances in one language. It's necessary to identify when the documentation refers to an `*object`* as one of the two definitions.
`*Database objects`* are concepts that refer to `F33f`_`[relational`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Relational_model]`_`f or sequential databases and persist being valid in new models. `*Tables`*, `*triggers`*, `*columns`*, `*indexes`* are examples of database objects,`:cite-ref-top10-7-1[`F5bf`_`[7`#cite-note-top10-7]`_`f] which are present in O-PL/SQL, but with the same meaning of the notion of `F33f`_`[Java`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Java_(programming_language)]`_`f objects, specifically an element of a set that has its existence beginning from an instantiation of a class.
>>The PL/SQL
PL/SQL is the extended SQL language used by `F33f`_`[Oracle Database`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Oracle_Database]`_`f.
PL/SQL is available in Oracle Database (since version 7), `F33f`_`[TimesTen in-memory database`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=TimesTen]`_`f (since version 11.2.1), and `F33f`_`[IBM Db2`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=IBM_Db2]`_`f (since version 9.7).`:cite-ref-ibm-11-0[`F5bf`_`[11`#cite-note-ibm-11]`_`f]
O-PL/SQL allows the definition of classes and instantiating these as objects, thus creating user-defined datatypes as writing constructors, beyond using Java in stored procedures and triggers.
>>Examples of uses of syntax of O-PL/SQL
Here is a small set of examples of O-PL/SQL syntax, extracted from the official documentation`:cite-ref-oradoc-12-0[`F5bf`_`[12`#cite-note-oradoc-12]`_`f] and other sources:
A simple example of object-oriented PL/SQL`:cite-ref-13[`F5bf`_`[13`#cite-note-13]`_`f]
`B100`F9d9create or replace type base_type as object (`f`b
`B100`F9d9 a number,`f`b
`B100`F9d9 constructor function base_type return self as result,`f`b
`B100`F9d9 member function func return number,`f`b
`B100`F9d9 member procedure proc (n number)`f`b
`B100`F9d9) instantiable not final;`f`b
`B100`F9d9/`f`b
Now, the type's implementation is created. The implementation defines how the type's functions, procedures and how explicit constructors behave:
`B100`F9d9create or replace type body base_type as`f`b
`B100`F9d9 constructor function base_type return self as result is`f`b
`B100`F9d9 begin`f`b
`B100`F9d9 a:=0;`f`b
`B100`F9d9 return;`f`b
`B100`F9d9 end base_type;`f`b
`B100`F9d9`f`b
`B100`F9d9 member function func return number is`f`b
`B100`F9d9 begin`f`b
`B100`F9d9 return a;`f`b
`B100`F9d9 end func;`f`b
`B100`F9d9`f`b
`B100`F9d9 member procedure proc (n number) as`f`b
`B100`F9d9 begin`f`b
`B100`F9d9 a:=n;`f`b
`B100`F9d9 end proc;`f`b
`B100`F9d9end;`f`b
`B100`F9d9/`f`b
We're ready to derive from base_type. The keyword for deriving is under. The derived type defines a new attribute (named: m) and overrides func.
`B100`F9d9create or replace type deriv_type under base_type (`f`b
`B100`F9d9 m number,`f`b
`B100`F9d9 overriding member function func return number`f`b
`B100`F9d9);`f`b
`B100`F9d9/`f`b
As is the case with base types, the overridden methods in the derived type must be implemented:
`B100`F9d9create or replace type body deriv_type as`f`b
`B100`F9d9 overriding member function func return number is`f`b
`B100`F9d9 begin`f`b
`B100`F9d9 return m*a;`f`b
`B100`F9d9 end;`f`b
`B100`F9d9end;`f`b
`B100`F9d9/`f`b
The created types can be instantiated and methods can be called:
`B100`F9d9declare`f`b
`B100`F9d9 b1 base_type :=base_type();`f`b
`B100`F9d9 b2 base_type :=base_type(4);`f`b
`B100`F9d9 d1 deriv_type:=deriv_type(5,6);`f`b
`B100`F9d9 d2 deriv_type:=deriv_type(5,6);`f`b
`B100`F9d9begin`f`b
`B100`F9d9 dbms_output.put_line(b1.func);`f`b
`B100`F9d9 dbms_output.put_line(b2.func);`f`b
`B100`F9d9`f`b
`B100`F9d9 d1.proc(4);`f`b
`B100`F9d9 dbms_output.put_line(d1.func);`f`b
`B100`F9d9 dbms_output.put_line(d2.func);`f`b
`B100`F9d9end;`f`b
`B100`F9d9/`f`b
Results
`B100`F9d90`f`b
`B100`F9d94`f`b
`B100`F9d924`f`b
`B100`F9d930`f`b
The created types have become real types and can be used in tables:
`B100`F9d9create table table_base (`f`b
`B100`F9d9 b base_type`f`b
`B100`F9d9);`f`b
`B100`F9d9declare`f`b
`B100`F9d9 base base_type := base_type();`f`b
`B100`F9d9 deriv deriv_type:= deriv_type(8,9);`f`b
`B100`F9d9begin`f`b
`B100`F9d9 insert into table_base values(base);`f`b
`B100`F9d9 insert into table_base values(deriv);`f`b
`B100`F9d9end;`f`b
`B100`F9d9/`f`b
`B100`F9d9select t.b.func() from table_base t;`f`b
Results:
`B100`F9d90`f`b
`B100`F9d972`f`b
`B100`F9d9select avg(t.b.func()) from table_base t;`f`b
Result:
`B100`F9d936`f`b
>>See also
• `F33f`_`[Relational database management system`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Relational_database_management_system]`_`f
• `F33f`_`[Component-oriented database`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Component-oriented_database]`_`f
• `F33f`_`[Transact-SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Transact-SQL]`_`f
• `F33f`_`[Database management system`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Database_management_system]`_`f
>>Bibliography
• `:citerefbennett2002`aBennett, Mathew (2002). `*Programming Oracle Developer`* (1 ed.). Indianapolis: Sams. p. 348. `F33f`_`[ISBN`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ISBN_(identifier)]`_`f 0672321106.
• `:citerefbales2007`aBales, Donals (2007). `*PL/SQL from Novice to Professional`* (1 ed.). New York: Apress. p. 469. `F33f`_`[ISBN`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ISBN_(identifier)]`_`f 978-1590598825.
• `:citereffeuersteinpribyl2009`aFeuerstein, Steven; Pribyl, Bill (2009). "26". `*Oracle PL/SQL Programming`*. Sebastopol: O'Reilly Media, Inc. `F33f`_`[ISBN`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ISBN_(identifier)]`_`f 9780596514464. Archived from the original on 2013-12-25. Retrieved 2012-04-20.
• `:citerefrahayutaniarpardede2006`aRahayu, Wenny; taniar, David; Pardede, Eric (2006). `*Object-Oriented Oracle`* (PDF). Hershey: IRM Press. p. 345. `F33f`_`[ISBN`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=ISBN_(identifier)]`_`f 1591406080. Archived from the original (PDF) on 2016-03-03. Retrieved 2012-04-20.
>>References
`:cite-note-citdk-1`!1.`! `F0af`_`[↑`#cite-ref-citdk-1-0]`_`f `:citereflassan-alan-r-due-jacob-steen2000`aLassan, Alan R.; Due, Jacob Steen (13 June 2000). "Experiences with Object Oriented Development in PL/SQL" (PDF). The danish National Center for IT Research. Archived from the original (PDF) on 24 December 2010. Retrieved 15 April 2012.
`:cite-note-2`!2.`! `F0af`_`[↑`#cite-ref-2]`_`f `:citerefcentre-forallan-r-lassenjacob-steen-due2000`aCentre For; Allan R. Lassen; Jacob Steen Due (2000). "Experiences with Object Oriented Development in PL/SQL". `F33f`_`[CiteSeerX`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=CiteSeerX_(identifier)]`_`f 10.1.1.38.5122. `B100`F9d9{{cite journal}}`f`b: Cite journal requires `B100`F9d9|journal=`f`b (help)
`:cite-note-dbaora-3`!3.`! `F0af`_`[↑`#cite-ref-dbaora-3-0]`_`f `:citerefcunningham-lewis`aCunningham, Lewis. "PL/SQL Features by Release". Burleson Consulting. Retrieved 15 April 2012.
`:cite-note-stkover-4`!4.`! `F0af`_`[↑`#cite-ref-stkover-4-0]`_`f "When Should you use Java Stored Procedures with an Oracle Database, what are the Drawbacks?". Stack Overflow. Retrieved 15 April 2012.
`:cite-note-etut-5`!5.`! `F0af`_`[↑`#cite-ref-etut-5-0]`_`f "Oracle's Object-Oriented Features". etutorial.org. Retrieved 16 April 2012.
`:cite-note-sams-6`!6.`! `F0af`_`[↑`#cite-ref-sams-6-0]`_`f Benett, 2002:144
`:cite-note-top10-7`!7.`! `F0af`_`[↑`#cite-ref-top10-7-0]`_`f `:citerefshubho-al-farooque2009`aShubho, Al-Farooque (8 November 2009). "Optimize Database Files and Apply Partitioning". The Code Project. Retrieved 19 April 2012.
`:cite-note-bales-8`!8.`! `F0af`_`[↑`#cite-ref-bales-8-0]`_`f Bales, 2007:107-209
`:cite-note-java2-9`!9.`! `F0af`_`[↑`#cite-ref-java2-9-0]`_`f "Use Object PL/SQL". java2s.com. Retrieved 19 April 2012.
`:cite-note-steve-10`!10.`! `F0af`_`[↑`#cite-ref-steve-10-0]`_`f Feuerstein, 2009
`:cite-note-ibm-11`!11.`! `F0af`_`[↑`#cite-ref-ibm-11-0]`_`f "DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows". IBM. Retrieved 20 April 2012.
`:cite-note-oradoc-12`!12.`! `F0af`_`[↑`#cite-ref-oradoc-12-0]`_`f "Oracle Documentatio". Oracle. Retrieved 19 April 2012.
`:cite-note-13`!13.`! `F0af`_`[↑`#cite-ref-13]`_`f "Object Oriented Oracle, example 1". René Nyffenegger's collection of things on the web. Retrieved 19 April 2012.
>>External sources
• Examples of O-Pl/SQL
• Another example of stored procedure in Java embedded in Oracle Documentation: "Calling Java Methods in Oracle Database". Oracle. Retrieved 20 April 2012.
`c`F0af`_`[↑ Back to top`#top]`_`f`a